package com.antoco.operation.bigscreen.repository;

import com.antoco.operation.bigscreen.model.entity.SensorReport;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.persistence.Tuple;
import java.util.List;


@Repository
public interface SensorReportRepository extends PagingAndSortingRepository<SensorReport, Long> {

    @Query(value = "select sensor_id, max(report_val), min(report_val), avg(report_val), count(*) from sensor_report where sensor_id = ? group by sensor_id", nativeQuery =  true)
    Tuple findHistoricalMinMaxAvgValueBySensorId(Long sensorId);

    @Query(value = "select min(report_val), max(report_val), avg(report_val), count(*) from sensor_report where sensor_id = :sensorId and " +
            "gen_time <= DATE_SUB(DATE_ADD(:date , INTERVAL 1 DAY),INTERVAL 1 SECOND) and gen_time >= DATE_FORMAT(:date,'%Y-%m-%d %H:%i:%s')", nativeQuery = true)
    Tuple findMinMaxAvgValueBySensorIdAndGenTime(@Param("sensorId") Long sensorId, @Param("date") String date);

    @Query(value = "select * from sensor_report where sensor_id = ?1 and gen_time >= ?2 and gen_time <= ?3 order by gen_time DESC",
            nativeQuery = true)
    List<SensorReport> findForTodayAndYestoday(Long sensorId, String startTime, String endTime);

    @Query(value = "SELECT * FROM sensor_report WHERE sensor_id = ?1 ORDER BY gen_time DESC",
            nativeQuery = true)
    List<SensorReport> findAllBySeneorId(Long sensorId);
}